from sqlalchemy import create_engine,func
from sqlalchemy.orm import sessionmaker, relationships
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Table,MetaData,Integer,String,Date,Boolean,or_,func,ForeignKey
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/mydb?charset=utf8',max_overflow=5)
Base = declarative_base()
Base.metadata.create_all(engine)


class Dept(Base):
    __tablename__='dept'
    DEPTNO = Column(Integer,primary_key=True,autoincrement=False)#部门编号
    DNAME = Column(String(14))#部门名称
    LOC = Column(String(13))#部门地址


class Emp(Base):
    __tablename__= 'emp'
    EMPNO = Column(Integer,primary_key=True,autoincrement=False)#工号
    ENAME = Column(String(10))#姓名
    JOB = Column(String(9))#工作部门
    MGB = Column(Integer)#经理
    HIREDATE = Column(Date)#入职时间
    SAL = Column(String)#薪水
    COMM = Column(String)#酬金
    DEPTNO = Column(Integer,ForeignKey(Dept.DEPTNO))#部门编号

class OrmDB(object):
    def __init__(self, username, password, host, port, dbname):
        sql = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(username,password,host,port,dbname)
        engine = create_engine(sql,max_overflow=5)
        Session = sessionmaker(bind=engine)#创建一个sessionmaker对象
        self.session = Session()

    def addObject(self):#创键对象
        obj = Emp(EMPNO=7369, ENAME='SMITH',JOB= 'CLERK',MGB= 7902,HIREDATE= '1980-12-17', SAL=800.00, COMM=None, DEPTNO=20)
        obj1 = Emp(EMPNO=7499,ENAME= 'ALLEN',JOB= 'SALESMAN',MGB= 7698,HIREDATE= '1981-02-20', SAL=1600.00, COMM=300.00, DEPTNO=30)
        Obj2 = Dept(DEPTNO=10,DNAME='ACCOUNTING',LOC='NEW YORK')
        self.session.add_all([obj,obj1,Obj2])
        self.session.commit()

    def search(self):
        # ret = self.session.query(Emp, Dept).filter(Emp.DEPTNO == Dept.DEPTNO).all()
        # ret = self.session.query(Emp, Dept).filter(Emp.DEPTNO == Dept.DEPTNO==20).all()
        ret = self.session.query(func.avg(Emp.SAL),Emp.DEPTNO).group_by(Emp.DEPTNO).order_by(func.avg(Emp.SAL).desc()).all()
        #此处  分组 排序有问题
        # ret = self.session.query(Emp).filter(or_(Emp.SAL<3000,Emp.HIREDATE>'1981-10-1')).order_by(Emp.SAL.desc(),Emp.HIREDATE).all()

        return ret



if __name__=='__main__':
    db = OrmDB('root','123456','localhost',3306,'mydb')
    # db.addObject()
    items = db.search()
    print(items)
    for i in items:
        print(i)
        # print(i.ENAME, i.EMPNO, i.SAL,i.HIREDATE)

    # for i in items:
'''2
        print(i[0].ENAME,i[0].EMPNO,i[0].SAL,i[0].MGB,i[0].JOB,i[1].DNAME,i[1].LOC)
'''
'''
        print(i[0].EMPNO,i[0].ENAME,i[0].JOB,i[0].MGB,i[0].SAL,i[0].HIREDATE,i[0].COMM,i[0].DEPTNO,i[1].DNAME)
'''


''''
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)DEPTNO, DNAME, LOC
2、使用SQLalchemy，实现所有员工的姓名、编号、薪资、岗位、部门名、部门地址
3、使用SQLalchemy，查询所有部门编号20的员工信息及部门名
4、使用SQLalchemy，查询所有部门的平均工资，并按照降序排列，采用函数如下：
Session.query(funct.avg(字段),obj.条件字段).group_by(条件字段).all()
5、使用SQLalchemy，查询所有员工的编号，员工姓名，岗位，领导编号，领导姓名，采用函数如下：session.query(obj1).join(obj2,isouter=True).all()，其中isouter代表是否为左外连接
6、使用SQLalchemy，查询岗位薪资低于3000元，或者入职时间晚于1981-10-01的员工姓名、编号，岗位，薪资、入职时间，同时对薪资降序和入职时间升序排列
'''
